/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seari.utils;

import com.seari.bean.DeviceList;
import com.seari.bean.DeviceType;
import com.seari.bean.DeviceTypeAttr;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

/**
 *
 * @author Rainbow
 */
public class DeviceListHelper
{
    public static final String MODIFY_FLAG = "m";
    public static final String DELETE_FLAG = "d";
    public static final String DEVICE_TYPE_BOOL = "BOOL";
    public static final String S7_DRIVER = "S7";
    public static final String MODBUS_DRIVER = "MODBUS";
    public static final String SYM_DRIVER = "SymGate";
    public static final String AI_IOTYPE = "AI";
    public static final String DI_IOTYPE = "DI";
    public static final String AO_IOTYPE = "AO";
    public static final String DO_IOTYPE = "DO";
    
    
     public Workbook generateWorkBook(Map<String,DeviceType> DeviceTypeMap,List<DeviceList> exportList ,String line)
    {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        int rowNum = generateDeviceType(sheet,DeviceTypeMap);
        rowNum = generateDataPoint(rowNum, sheet, exportList);
        rowNum = generateComments(rowNum, sheet, exportList);
        rowNum = generateDPValue(rowNum, sheet, exportList);
        rowNum = generateAlertValue(rowNum, sheet, exportList);
        rowNum = generateDistributionInfo(rowNum, sheet, exportList,line);
        rowNum = generatePeriphAddrMain(rowNum, sheet, exportList ,line);
        rowNum = generateDbArchiveInfo(rowNum, sheet, exportList);
        return wb;
    }
    
    public int generateDeviceType(Sheet sheet , Map<String,DeviceType> DeviceTypeMap)
    {
        int currentRow = 4;
        int numSeq = 2;
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("# ascii dump of database");
        sheet.createRow(1);//空行
        sheet.createRow(2).createCell(0).setCellValue("# DpType");
        sheet.createRow(3).createCell(0).setCellValue("TypeName");
        for (Entry<String, DeviceType> mapEntry : DeviceTypeMap.entrySet())
        {
            DeviceType deviceType = mapEntry.getValue();
            Map<String, List<DeviceTypeAttr>> attrMap = new HashMap<String, List<DeviceTypeAttr>>();
            sheet.createRow(currentRow).createCell(0).setCellValue(deviceType.getDeviceName()+"."+deviceType.getDeviceName());
            sheet.getRow(currentRow).createCell(1).setCellValue("1#1");numSeq = 2;currentRow++;
            for (DeviceTypeAttr attr : deviceType.getAttrList())
            {
                    String attrType = attr.getType();
                    if(attrMap.containsKey(attrType))
                    {
                            List<DeviceTypeAttr> attrList = attrMap.get(attrType);
                            attrList.add(attr);
                    }
                    else
                    {
                            List<DeviceTypeAttr> attrList2 = new ArrayList<DeviceTypeAttr>();
                            attrList2.add(attr);
                            attrMap.put(attrType, attrList2);
                    }
            }
            for (Entry<String, List<DeviceTypeAttr>> entry: attrMap.entrySet())
            {
                    Row newRow = sheet.createRow(currentRow);
                    //取出的KEY为attr的type,单列一行,并且以1#开头
                    newRow.createCell(0);
                    newRow.createCell(1).setCellValue(entry.getKey());
                    newRow.createCell(2).setCellValue("1#"+numSeq);numSeq++;currentRow++;
                    for(DeviceTypeAttr attr : entry.getValue())
                    {
                            Row newRow2 = sheet.createRow(currentRow);
                            newRow2.createCell(0);
                            newRow2.createCell(1);
                            newRow2.createCell(2).setCellValue(attr.getIndex());
                            String dataType = getAttrDataType(attr.getDataType());
                            newRow2.createCell(3).setCellValue(dataType+"#"+numSeq);numSeq++;currentRow++;
                    }
            }
            
        }
        return currentRow;
    }
    
    public int generateDataPoint(int rowNum ,Sheet sheet, List<DeviceList> exportList)
    {
        int startID = 60000;
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# Datapoint/DpId");rowNum++;
        Row newRow1  = sheet.createRow(rowNum);
        newRow1.createCell(0).setCellValue("DpName");
        newRow1.createCell(1).setCellValue("TypeName");
        newRow1.createCell(2).setCellValue("ID");
        rowNum++;
        //表体
        for (DeviceList deviceList : exportList)
        {
            Row tableBody = sheet.createRow(rowNum);
            tableBody.createCell(0).setCellValue(deviceList.getDevIndex());
            tableBody.createCell(1).setCellValue(deviceList.getDevID());
            tableBody.createCell(2).setCellValue(startID);
            rowNum++;startID++;
        }
        return rowNum;
    }
    
    public int generateComments(int rowNum,Sheet sheet,List<DeviceList> exportList)
    {
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# Aliases/Comments");rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("AliasId");
        newRow.createCell(1).setCellValue("AliasName");
        newRow.createCell(2).setCellValue("CommentName");
        rowNum++;
        //表体
        for (DeviceList deviceList : exportList)
        {
            if(null != deviceList.getDeviceType() && !CollectionUtils.isEmpty(deviceList.getDeviceType().getAttrList()))
            {
                for (DeviceTypeAttr deviceTypeAttr : deviceList.getDeviceType().getAttrList())
                {
//                    if("1".equals(deviceTypeAttr.geteAlarm())||"1".equals(deviceTypeAttr.geteControl()))
//                    {
                        Row tableRow = sheet.createRow(rowNum);
                        tableRow.createCell(0).setCellValue(deviceList.getDevIndex()+"."+deviceTypeAttr.getType()+"."+deviceTypeAttr.getIndex());
                        //tableRow.createCell(2).setCellValue("lt:2 LANG:10001 \"@@\" LANG:10004 \""+deviceList.getDevAddress()+deviceList.getDevName()+deviceList.getSbdm()+deviceTypeAttr.getDesc()+"@@\"");
                        tableRow.createCell(2).setCellValue(deviceList.getDevAddress()+deviceList.getDevName()+deviceList.getSbdm()+deviceTypeAttr.getDesc());
                        rowNum++;
//                    }
                }
            }
        }
        return rowNum;
    }
    
    public int generateDPValue(int rowNum,Sheet sheet,List<DeviceList> exportList)
    {
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# DpValue");rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("_original.._value");
        newRow.createCell(4).setCellValue("_original.._status64");
        newRow.createCell(5).setCellValue("_original.._stime");
        rowNum++;
        //表体
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss:SSS");
        String now = dateFormat.format(new Date());
        for (DeviceList deviceList : exportList)
        {
            Map<String, List<DeviceTypeAttr>> attrMap = new HashMap<String, List<DeviceTypeAttr>>();
            for (DeviceTypeAttr attr : deviceList.getDeviceType().getAttrList())
            {
                    String attrType = attr.getType();
                    if(attrMap.containsKey(attrType))
                    {
                            List<DeviceTypeAttr> attrList = attrMap.get(attrType);
                            attrList.add(attr);
                    }
                    else
                    {
                            List<DeviceTypeAttr> attrList2 = new ArrayList<DeviceTypeAttr>();
                            attrList2.add(attr);
                            attrMap.put(attrType, attrList2);
                    }
            }
            for (Entry<String, List<DeviceTypeAttr>> entry : attrMap.entrySet())
            {
                for (DeviceTypeAttr deviceTypeAttr : entry.getValue())
                {
                    Row tableRow = sheet.createRow(rowNum);
                    tableRow.createCell(0).setCellValue("ASC (1)/0");
                    tableRow.createCell(1).setCellValue(deviceList.getDevIndex()+"."+entry.getKey()+"."+ deviceTypeAttr.getIndex());
                    tableRow.createCell(2).setCellValue(deviceList.getDevID());
                    if("PPY".equals(entry.getKey().toUpperCase()))
                    {
                        if("IDN".equals(deviceTypeAttr.getIndex().toUpperCase()))
                        {
                            tableRow.createCell(3).setCellValue(deviceList.getSbdm());
                        }
                        else if("LON".equals(deviceTypeAttr.getIndex().toUpperCase()))
                        {
                            tableRow.createCell(3).setCellValue(deviceList.getDevAddress());
                        }
                        else if("NAM".equals(deviceTypeAttr.getIndex().toUpperCase()))
                        {
                            tableRow.createCell(3).setCellValue(deviceList.getDevName());
                        }else{
                            tableRow.createCell(3).setCellValue(0);
                        }
                    }else{
                        tableRow.createCell(3).setCellValue(0);
                    }
                    tableRow.createCell(4).setCellValue("0x8300000000100101");
                    tableRow.createCell(5).setCellValue(now);
                    rowNum++;
                }
            }
            
        }
        return rowNum;
    }
    
    public int generateAlertValue(int rowNum, Sheet sheet,List<DeviceList> exportList)
    {
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# AlertValue");rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("DetailNr");
        newRow.createCell(4).setCellValue("_alert_hdl.._type");
        newRow.createCell(5).setCellValue("_alert_hdl.._l_limit");
        newRow.createCell(6).setCellValue("_alert_hdl.._u_limit");
        newRow.createCell(7).setCellValue("_alert_hdl.._l_incl");
        newRow.createCell(8).setCellValue("_alert_hdl.._u_incl");
        newRow.createCell(9).setCellValue("_alert_hdl.._panel");
        newRow.createCell(10).setCellValue("_alert_hdl.._panel_param");
        newRow.createCell(11).setCellValue("_alert_hdl.._help");
        newRow.createCell(12).setCellValue("_alert_hdl.._min_prio");
        newRow.createCell(13).setCellValue("_alert_hdl.._class");
        newRow.createCell(14).setCellValue("_alert_hdl.._text");
        newRow.createCell(15).setCellValue("_alert_hdl.._active");
        newRow.createCell(16).setCellValue("_alert_hdl.._orig_hdl");
        newRow.createCell(17).setCellValue("_alert_hdl.._ok_range");
        newRow.createCell(18).setCellValue("_alert_hdl.._hyst_type");
        newRow.createCell(19).setCellValue("_alert_hdl.._hyst_time");
        newRow.createCell(20).setCellValue("_alert_hdl.._multi_instance");
        newRow.createCell(21).setCellValue("_alert_hdl.._l_hyst_limit");
        newRow.createCell(22).setCellValue("_alert_hdl.._u_hyst_limit");
        newRow.createCell(23).setCellValue("_alert_hdl.._text1");
        newRow.createCell(24).setCellValue("_alert_hdl.._text0");
        newRow.createCell(25).setCellValue("_alert_hdl.._ack_has_prio");
        newRow.createCell(26).setCellValue("_alert_hdl.._order");
        newRow.createCell(27).setCellValue("_alert_hdl.._dp_pattern");
        newRow.createCell(28).setCellValue("_alert_hdl.._dp_list");
        newRow.createCell(29).setCellValue("_alert_hdl.._prio_pattern");
        newRow.createCell(30).setCellValue("_alert_hdl.._abbr_pattern");
        newRow.createCell(31).setCellValue("_alert_hdl.._ack_deletes");
        newRow.createCell(32).setCellValue("_alert_hdl.._non_ack");
        newRow.createCell(33).setCellValue("_alert_hdl.._came_ack");
        newRow.createCell(34).setCellValue("_alert_hdl.._pair_ack");
        newRow.createCell(35).setCellValue("_alert_hdl.._both_ack");
        newRow.createCell(36).setCellValue("_alert_hdl.._impulse");
        newRow.createCell(37).setCellValue("_alert_hdl.._filter_threshold");
        newRow.createCell(38).setCellValue("_alert_hdl.._went_text");
        newRow.createCell(39).setCellValue("_alert_hdl.._add_text");
        newRow.createCell(40).setCellValue("_alert_hdl.._status64_pattern");
        newRow.createCell(41).setCellValue("_alert_hdl.._neg");
        newRow.createCell(42).setCellValue("_alert_hdl.._status64_match");
        newRow.createCell(43).setCellValue("_alert_hdl.._match");
        newRow.createCell(44).setCellValue("_alert_hdl.._set");
        rowNum++;
        //表体
        for (DeviceList deviceList : exportList)
        {
            if(null != deviceList.getDeviceType() && !CollectionUtils.isEmpty(deviceList.getDeviceType().getAttrList()))
            {
                for (DeviceTypeAttr deviceTypeAttr : deviceList.getDeviceType().getAttrList())
                {
                    if("1".equals(deviceTypeAttr.geteAlarm()))
                    {
                        Row tableRow = sheet.createRow(rowNum);
                        tableRow.createCell(0).setCellValue("ASC (1)/0");
                        tableRow.createCell(1).setCellValue(deviceList.getDevIndex()+"."+deviceTypeAttr.getType()+"."+deviceTypeAttr.getIndex());
                        tableRow.createCell(2).setCellValue(deviceList.getDevID());
                        tableRow.createCell(3).setCellValue(""); //detailNO
                        if(DEVICE_TYPE_BOOL.equals(deviceTypeAttr.getDataType()))
                        {
                            tableRow.createCell(4).setCellValue("12");
                        }
                        tableRow.createCell(11).setCellValue("lt:2 LANG:10001 \"\" LANG:10004 \"\" "); //_alert_hdl.._help
                        tableRow.createCell(12).setCellValue("\\0"); //_alert_hdl.._min_prio
                        tableRow.createCell(13).setCellValue(getAttrAlarmtLevel(deviceTypeAttr.getAlarmLevel()));
                        tableRow.createCell(15).setCellValue("1"); //_alert_hdl.._active
                        tableRow.createCell(16).setCellValue("1"); //_alert_hdl.._orig_hdl
                        if(StringUtils.isNotBlank(deviceTypeAttr.getAlarmType())) //_alert_hdl.._ok_range
                        {
                            if("1".equals(deviceTypeAttr.getAlarmType()))
                            {
                                tableRow.createCell(17).setCellValue("0");
                            }
                            else if("0".equals(deviceTypeAttr.getAlarmType()))
                            {
                                tableRow.createCell(17).setCellValue("1");
                            }
                            
                        }
                        tableRow.createCell(18).setCellValue("0");  //_alert_hdl.._hyst_type
                        tableRow.createCell(19).setCellValue("01.01.1970 00:00:00.000");  //_alert_hdl.._hyst_time
                        tableRow.createCell(20).setCellValue("0"); //_alert_hdl.._multi_instance
                        tableRow.createCell(23).setCellValue("lt:2 LANG:10001 \"\" LANG:10004 \""+deviceTypeAttr.getDigit1()+"\" "); //_alert_hdl.._text1
                        tableRow.createCell(24).setCellValue("lt:2 LANG:10001 \"\" LANG:10004 \""+deviceTypeAttr.getDigit0()+"\" "); //_alert_hdl.._text0
                        tableRow.createCell(36).setCellValue("0"); //_alert_hdl.._impulse
                        rowNum++;
                    }
                }
                
            }
        }
        return  rowNum;
    }
    
    public int generateDistributionInfo(int rowNum, Sheet sheet,List<DeviceList> exportList,String line)
    {
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# DistributionInfo");rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("_distrib.._type");
        newRow.createCell(4).setCellValue("_distrib.._driver");
        rowNum++;
        //表体
        for (DeviceList deviceList : exportList)
        {
            if(null != deviceList.getDeviceType() && !CollectionUtils.isEmpty(deviceList.getDeviceType().getAttrList()))
            {
                for (DeviceTypeAttr deviceTypeAttr : deviceList.getDeviceType().getAttrList())
                {
                    if("N".equals(deviceTypeAttr.getIscsInfo().toUpperCase()))
                    {
                        continue;
                    }
                    else
                    {
                        Row tableRow = sheet.createRow(rowNum);
                        tableRow.createCell(0).setCellValue("ASC (1)/0");
                        tableRow.createCell(1).setCellValue(deviceList.getDevIndex()+"."+deviceTypeAttr.getType()+"."+deviceTypeAttr.getIndex());
                        tableRow.createCell(2).setCellValue(deviceList.getDevID());
                        tableRow.createCell(3).setCellValue(56); //_distrib.._type
                        if(deviceList.getDriverType().equals(SYM_DRIVER) && line.toUpperCase().equals("M") && deviceList.getSysID().equals("FASX"))
                        {
                        	//13号线FAS FEP驱动号为5
                        	tableRow.createCell(4).setCellValue("\\5"); //_distrib.._driver
                        }else if (deviceList.getDriverType().equals(SYM_DRIVER) && line.toUpperCase().equals("M") && deviceList.getSysID().equals("AFCX")) {
                        	//13号线AFC FEP驱动号为5
                        	tableRow.createCell(4).setCellValue("\\5"); //_distrib.._driver
						}else {
                        	tableRow.createCell(4).setCellValue("\\4"); //_distrib.._driver
						}
                        rowNum++;
                    }
                }
            }
        }
        return rowNum;
    }
    
     public int generatePeriphAddrMain(int rowNum, Sheet sheet,List<DeviceList> exportList,String line)
     {
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# PeriphAddrMain");rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("_address.._type");
        newRow.createCell(4).setCellValue("_address.._reference");
        newRow.createCell(5).setCellValue("_address.._poll_group");
        newRow.createCell(6).setCellValue("_address.._connection");
        newRow.createCell(7).setCellValue("_address.._offset");
        newRow.createCell(8).setCellValue("_address.._subindex");
        newRow.createCell(9).setCellValue("_address.._direction");
        newRow.createCell(10).setCellValue("_address.._internal");
        newRow.createCell(11).setCellValue("_address.._lowlevel");
        newRow.createCell(12).setCellValue("_address.._active");
        newRow.createCell(13).setCellValue("_address.._start");
        newRow.createCell(14).setCellValue("_address.._interval");
        newRow.createCell(15).setCellValue("_address.._reply");
        newRow.createCell(16).setCellValue("_address.._datatype");
        newRow.createCell(17).setCellValue("_address.._drv_ident");
        rowNum++;
        //表体
        for (DeviceList deviceList : exportList)
        {
            if(null != deviceList.getDeviceType() && !CollectionUtils.isEmpty(deviceList.getDeviceType().getAttrList()))
            {
                for (DeviceTypeAttr deviceTypeAttr : deviceList.getDeviceType().getAttrList())
                {
                    if("N".equals(deviceTypeAttr.getIscsInfo().toUpperCase()) || "1".equals(deviceTypeAttr.getIscsInfo()))
                    {
                        continue;
                    }
                    else
                    {
                        Row tableRow = sheet.createRow(rowNum);
                        tableRow.createCell(0).setCellValue("ASC (1)/0");
                        tableRow.createCell(1).setCellValue(deviceList.getDevIndex()+"."+deviceTypeAttr.getType()+"."+deviceTypeAttr.getIndex());
                        tableRow.createCell(2).setCellValue(deviceList.getDevID());
                        tableRow.createCell(3).setCellValue(16); //_address.._type
                        /*
                         * 13号线WINCCOA FAS点表生成，目前FAS点表需要连接FEP驱动而非MODBUS，因此生成WINCCOA modbus驱动点功能暂时无用
                         */
                        if(deviceList.getDriverType().toUpperCase().equals(MODBUS_DRIVER) && line.toUpperCase().equals("M") && deviceList.getSysID().equals("FASX"))
                        {
                        	try
							{
                        		tableRow.createCell(4).setCellValue("M.1.1." + (Integer.parseInt(deviceList.getBeizhu2()) + Integer.parseInt(deviceTypeAttr.getIscsAddr()) + Integer.parseInt(deviceTypeAttr.getIscsBit()))); //_address.._reference
							} catch (Exception e)
							{
								// TODO: handle exception
								continue;
							}
                        	
                            tableRow.createCell(5).setCellValue("_P1S"); //_address.._poll_group
                            tableRow.createCell(6); //_address.._connection
                            tableRow.createCell(7).setCellValue(0); //_address.._connection
                            tableRow.createCell(8).setCellValue(0); //_address.._subindex
                            if(deviceTypeAttr.getIoType().toUpperCase().equals(AI_IOTYPE) || deviceTypeAttr.getIoType().toUpperCase().equals(DI_IOTYPE))
                            {
                            	tableRow.createCell(9).setCellValue("\\4"); //_address.._direction
                            }else {
                            	tableRow.createCell(9).setCellValue("\\1"); //_address.._direction
							}
                            tableRow.createCell(10).setCellValue(0); //_address.._internal
                            tableRow.createCell(11).setCellValue(0); //_address.._lowlevel
                            tableRow.createCell(12).setCellValue(1); //_address.._active
                            tableRow.createCell(13).setCellValue("01.01.1970 00:00:00.000"); //_address.._start
                            tableRow.createCell(14).setCellValue("01.01.1970 00:00:00.000"); //_address.._interval
                            tableRow.createCell(15).setCellValue("01.01.1970 00:00:00.000"); //_address.._reply
                            tableRow.createCell(16).setCellValue(567); //_address.._datatype
                            tableRow.createCell(17).setCellValue(MODBUS_DRIVER); //_address.._drv_ident
                        }else if(deviceList.getDriverType().equals(SYM_DRIVER) && line.toUpperCase().equals("M") && deviceList.getSysID().equals("FASX"))
                        {
                        	//13号线FAS 旋思驱动点生成
                        	String deviceId = deviceList.getDevIndex().split("_")[1];
                        	tableRow.createCell(4).setCellValue(deviceList.getStationID() + "FAS.FAS." + deviceId + "_" + deviceTypeAttr.getIndex()); //_address.._reference
                            tableRow.createCell(5); //_address.._poll_group
                            tableRow.createCell(6); //_address.._connection
                            tableRow.createCell(7).setCellValue(0); //_address.._connection
                            tableRow.createCell(8).setCellValue(0); //_address.._subindex
                            tableRow.createCell(9).setCellValue("\\2"); //_address.._direction
                            tableRow.createCell(10).setCellValue(0); //_address.._internal
                            tableRow.createCell(11).setCellValue(0); //_address.._lowlevel
                            tableRow.createCell(12).setCellValue(1); //_address.._active
                            tableRow.createCell(13).setCellValue("01.01.1970 00:00:00.000"); //_address.._start
                            tableRow.createCell(14).setCellValue("01.01.1970 00:00:00.000"); //_address.._interval
                            tableRow.createCell(15).setCellValue("01.01.1970 00:00:00.000"); //_address.._reply
                            tableRow.createCell(16).setCellValue(9); //_address.._datatype
                            tableRow.createCell(17).setCellValue(SYM_DRIVER); //_address.._drv_ident
                        }else if(deviceList.getDriverType().equals(SYM_DRIVER) && line.toUpperCase().equals("M") && deviceList.getSysID().equals("AFCX"))
                        {
                        	//13号线AFC 旋思驱动点生成
                        	String deviceId = deviceList.getDevIndex().split("_")[1];
                        	tableRow.createCell(4).setCellValue(deviceList.getStationID() + "AFC.AFC." + deviceId + "_" + deviceTypeAttr.getIndex()); //_address.._reference
                            tableRow.createCell(5); //_address.._poll_group
                            tableRow.createCell(6); //_address.._connection
                            tableRow.createCell(7).setCellValue(0); //_address.._connection
                            tableRow.createCell(8).setCellValue(0); //_address.._subindex
                            tableRow.createCell(9).setCellValue("\\2"); //_address.._direction
                            tableRow.createCell(10).setCellValue(0); //_address.._internal
                            tableRow.createCell(11).setCellValue(0); //_address.._lowlevel
                            tableRow.createCell(12).setCellValue(1); //_address.._active
                            tableRow.createCell(13).setCellValue("01.01.1970 00:00:00.000"); //_address.._start
                            tableRow.createCell(14).setCellValue("01.01.1970 00:00:00.000"); //_address.._interval
                            tableRow.createCell(15).setCellValue("01.01.1970 00:00:00.000"); //_address.._reply
                            tableRow.createCell(16).setCellValue(9); //_address.._datatype
                            tableRow.createCell(17).setCellValue(SYM_DRIVER); //_address.._drv_ident
                        }
                        else if (deviceList.getDriverType().equals(S7_DRIVER)) {
                        	tableRow.createCell(4).setCellValue("EMCS." + deviceList.getDeviceSequence() + "." + deviceTypeAttr.getIscsAddr()); //_address.._reference
                            tableRow.createCell(5).setCellValue("_P1S"); //_address.._poll_group
                            tableRow.createCell(6); //_address.._connection
                            tableRow.createCell(7).setCellValue(0); //_address.._connection
                            tableRow.createCell(8).setCellValue(0); //_address.._subindex
                            if(deviceTypeAttr.getIoType().toUpperCase().equals(AI_IOTYPE) || deviceTypeAttr.getIoType().toUpperCase().equals(DI_IOTYPE))
                            {
                            	tableRow.createCell(9).setCellValue("\\4"); //_address.._direction
                            }else {
                            	tableRow.createCell(9).setCellValue("\\1"); //_address.._direction
							}
                            tableRow.createCell(10).setCellValue(0); //_address.._internal
                            tableRow.createCell(11).setCellValue(0); //_address.._lowlevel
                            tableRow.createCell(12).setCellValue(1); //_address.._active
                            tableRow.createCell(13).setCellValue("01.01.1970 00:00:00.000"); //_address.._start
                            tableRow.createCell(14).setCellValue("01.01.1970 00:00:00.000"); //_address.._interval
                            tableRow.createCell(15).setCellValue("01.01.1970 00:00:00.000"); //_address.._reply
                            tableRow.createCell(16).setCellValue(getS7DataType(deviceTypeAttr.getDataType())); //_address.._datatype
                            tableRow.createCell(17).setCellValue(S7_DRIVER); //_address.._drv_ident
						}else {
                        	tableRow.createCell(4).setCellValue(deviceTypeAttr.getIscsAddr()); //_address.._reference
                            tableRow.createCell(5).setCellValue("_P1S"); //_address.._poll_group
                            tableRow.createCell(6); //_address.._connection
                            tableRow.createCell(7).setCellValue(0); //_address.._connection
                            tableRow.createCell(8).setCellValue(0); //_address.._subindex
                            tableRow.createCell(9).setCellValue("\\4"); //_address.._direction
                            tableRow.createCell(10).setCellValue(0); //_address.._internal
                            tableRow.createCell(11).setCellValue(0); //_address.._lowlevel
                            tableRow.createCell(12).setCellValue(1); //_address.._active
                            tableRow.createCell(13).setCellValue("01.01.1970 00:00:00.000"); //_address.._start
                            tableRow.createCell(14).setCellValue("01.01.1970 00:00:00.000"); //_address.._interval
                            tableRow.createCell(15).setCellValue("01.01.1970 00:00:00.000"); //_address.._reply
                            tableRow.createCell(16).setCellValue(567); //_address.._datatype
                            tableRow.createCell(17).setCellValue(deviceTypeAttr.getIoSource()); //_address.._drv_ident
                        }
                        
                        rowNum++;
                    }
                }
            }
        }
        return rowNum;
    }
     
     public int generateDbArchiveInfo(int rowNum, Sheet sheet,List<DeviceList> exportList)
     {
        //生成空白行
        sheet.createRow(rowNum);rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# DbArchiveInfo");rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("DetailNr");
        newRow.createCell(4).setCellValue("_archive.._type");
        newRow.createCell(5).setCellValue("_archive.._archive");
        newRow.createCell(6).setCellValue("_archive.._class");
        newRow.createCell(7).setCellValue("_archive.._interv");
        newRow.createCell(8).setCellValue("_archive.._interv_type");
        newRow.createCell(9).setCellValue("_archive.._std_type");
        newRow.createCell(10).setCellValue("_archive.._std_tol");
        newRow.createCell(11).setCellValue("_archive.._std_time");
        newRow.createCell(12).setCellValue("_archive.._round_val");
        newRow.createCell(13).setCellValue("_archive.._round_inv");
        rowNum++;
        //表体
        for (DeviceList deviceList : exportList)
        {
            if(null != deviceList.getDeviceType() && !CollectionUtils.isEmpty(deviceList.getDeviceType().getAttrList()))
            {
                for (DeviceTypeAttr deviceTypeAttr : deviceList.getDeviceType().getAttrList())
                {
                    if("1".equals(deviceTypeAttr.geteTrend()) || "1".equals(deviceTypeAttr.geteEvent()))
                    {
                        Row tableRow = sheet.createRow(rowNum);
                        tableRow.createCell(0).setCellValue("ASC (1)/0");
                        tableRow.createCell(1).setCellValue(deviceList.getDevIndex()+"."+deviceTypeAttr.getType()+"."+deviceTypeAttr.getIndex());
                        tableRow.createCell(2).setCellValue(deviceList.getDevID());
                        tableRow.createCell(3); //detailNO
                        tableRow.createCell(4).setCellValue(45); //_archive.._type
                        tableRow.createCell(5).setCellValue(1); //_archive.._archive
                        rowNum++;
                        Row tableRow2 = sheet.createRow(rowNum);
                        tableRow2.createCell(0).setCellValue("ASC (1)/0");
                        tableRow2.createCell(1).setCellValue(deviceList.getDevIndex()+"."+deviceTypeAttr.getType()+"."+deviceTypeAttr.getIndex());
                        tableRow2.createCell(2).setCellValue(deviceList.getDevID());
                        tableRow2.createCell(3).setCellValue(1); //detailNO
                        tableRow2.createCell(4).setCellValue(15); //_archive.._type
                        tableRow2.createCell(5); //_archive.._archive
                        tableRow2.createCell(6).setCellValue("_ValueArchive_1"); //_archive.._class
                        tableRow2.createCell(7).setCellValue(0); //_archive.._interv
                        tableRow2.createCell(8).setCellValue(0); //_archive.._interv_type
                        tableRow2.createCell(9).setCellValue(0); //_archive.._std_type
                        tableRow2.createCell(10).setCellValue(0); //_archive.._std_tol
                        tableRow2.createCell(11).setCellValue("01.01.1970 00:00:00.000"); 
                        tableRow2.createCell(12).setCellValue(0); //_archive.._round_val
                        tableRow2.createCell(13).setCellValue(0); //_archive.._round_inv
                        rowNum++;
                    }
                }
            }
        }
        return rowNum;
     }
    
	//获取deviceType所对应的属性数值
	public static String getAttrDataType(String source)
	{
		String result = "";
		try
		{
			source = source.toUpperCase();
			switch (source)
			{
			case "STRING":
				result = "25";
				break;
			case "BOOL":
				result = "23";
				break;
			case "INT":
				result = "21";
				break;
			case "FLOAT":
				result = "22";
				break;
			case "UINT":
				result = "20";
				break;
			default:
				break;
			}
		} catch (Exception e)
		{
			// TODO: handle exception
			e.printStackTrace();
		}

		return result;
	}
	
	public static String getS7DataType(String dataType)
	{
		String result = "";
		dataType = dataType.toUpperCase();
		switch (dataType)
		{
			case "BOOL":
				result = "706";
				break;
			case "INT":
				result = "701";
				break;
			case "FLOAT":
				result = "705";
				break;
			default:
				break;
		}
		return result;
	}
        
	public static String getAttrAlarmtLevel(String source)
	{
		String result = "";
		try
		{
			switch (source)
			{
			case "1":
				result = "a_low.";
				break;
			case "2":
				result = "a_mid.";
				break;
			case "3":
				result = "a_hig.";
				break;
			case "4":
				result = "a_emg.";
				break;
			default:
				result = "a_low.";
				break;
			}
		} catch (Exception e)
		{
		}
		return result;
	}
}
